#! /usr/bin/env python
# -*- coding:utf-8 -*-
import logging
import pandas as pd
import numpy as np
import configparser
import os, sys
import math
import datetime
import load_info as load
sys.path.append( sys.path[0])
import mysql_conn
config = configparser.ConfigParser(allow_no_value=True)
cur_path = os.getcwd()
from mysql_conn import Session
logger = logging.getLogger()
"""
实现数据处理功能 加工特征构造数据集、数据转换、输出到数据库等
结构：
    class Dataset_util :处理数据集,生成特征
    class Device_util  :策略相关的数据处理
    class To_database  :输出到数据库
"""
class Dataset_util:
    """
    加工特征,构造数据集
    """
    def __init__(self,dev_code):
        self.dev_code = dev_code         #'YC0053'
    #节假日信息特征
    def create_holiday(self, start_date, end_date, filepath='holiday.csv'):
        """
        获取节假日信息 0 工作日,1 周末,2 长假
        :param start_date: 开始时间
        :param end_date:   结束时间
        :param filepath:   'holiday.csv' 文件名称
        :return:holiday,df,节假日标记表
        """
        holiday = pd.read_csv(filepath)
        holiday.columns = ["sdate", "isholiday"]
        holiday = holiday[(holiday["sdate"] >= start_date) & (holiday["sdate"] <= end_date)]
        holiday["sdate"] = holiday["sdate"].apply(str)
        holiday["sdate"] = pd.to_datetime(holiday["sdate"])
        if holiday.empty:
            logger.error('获取节假日失败，请检查配置文件')
        return (holiday)
    #季节信息特征
    def create_seasons(self, start_date, end_date):
        """
        获取季节信息 2 过渡季，1 供冷季，3 供暖季
        :param start_date:开始时间
        :param end_date:结束时间
        :return:df
        """
        db = mysql_conn.db_con()
        pd.set_option('display.max_columns', None)
        # 显示所有行，把列显示设置成最大
        pd.set_option('display.max_rows', None)
        # 取图谱信息,获取时间段
        sqlstr = "select graph_start_time,graph_end_time,graph_type from graph_info where graph_type<>2"
        graph = pd.read_sql(sqlstr, db)
        db.close()
        if graph.empty:
            logger.error('查询图谱信息失败')
        df = pd.DataFrame()
        df["sdate"] = pd.date_range(start_date, end_date)
        df["num_date"] = (df['sdate'].dt.strftime('%m') + df['sdate'].dt.strftime('%d')).astype('int64')
        df['seasons'] = 2
        graph["num_sdate"] = graph["graph_start_time"].str.replace('-', '').astype('int64')
        graph["num_edate"] = graph["graph_end_time"].str.replace('-', '').astype('int64')
        for index, row in graph.iterrows():
            #处理跨年情况
            if row["num_sdate"] > row["num_edate"]:
                s1, s2, s3, s4 = row["num_sdate"], 1231, 101, row["num_edate"]
                df.loc[(df['num_date'] <= s2) & (df['num_date'] >= s1), ['seasons']] = row["graph_type"]
                df.loc[(df['num_date'] <= s4) & (df['num_date'] >= s3), ['seasons']] = row["graph_type"]
            else:
                s1, s2 = row["num_sdate"], row["num_edate"]
                df.loc[(df['num_date'] <= s2) & (df['num_date'] >= s1), ['seasons']] = row["graph_type"]
        df.drop(['num_date'], axis=1, inplace=True)
        if df.empty:
            logger.error('获取季节失败')
        return df
    #年中周数特征
    def create_weekyear(self, start_date, end_date):
        """
        获取年中第几周
        :param start_date:开始时间
        :param end_date:结束时间
        :return:df
        """
        df = pd.DataFrame()
        df["sdate"] = pd.date_range(start_date, end_date)
        df["weekyear"] = df["sdate"].dt.weekofyear
        if df.empty:
            logger.info('获取年中周数失败')
        return df
    #weekday特征-表示周几
    def create_weekdays(self, data):  # 表示周几
        """
        增加weekday列 int 表示周几
        :param data: df,load_data 的输出X
        """
        data["sdate"] = [np.datetime64(i) for i in data["sdate"].tolist()] #sdate列数据类型转换
        data["weekday"] = data["sdate"].dt.weekday #根据sdate列获取周几特征
    #holiday特征-表示是否为节假日
    def create_holidays(self, data):
        """
        添加holiday列 int,0~2 分别表示工作日 节假日 长假
        :param data: df,load_data 的输出X
        """
        holiday = pd.read_csv(os.getcwd() + '/common/holiday.csv') #读入节假日标识表
        if holiday.empty:
            logger.error('读取节假日失败')
        holi_date = holiday["SDATE"].tolist()   #holiday 表的date列
        is_holi = holiday["IsHoliday"].tolist() #holiday 表的节假日标识列
        holiday_list = [] #存储节假日标识
        for i in [int(i.strftime('%Y%m%d')) for i in data["sdate"]]: #对于sdate列的每个值
            item = is_holi[holi_date.index(int(i))] #获取节假日标识
            holiday_list.append(item)
        data["holiday"] = holiday_list
    #weekyear特征-表示一年中的第几周
    def create_week_order(self, data):
        """
        添加weekyear列 int 表示一年中的第几周
        :param data: df,load_data 的输出X
        """
        if data.empty:
            logger.error('传入数据为空')
        week_order = [math.ceil(i.timetuple().tm_yday / 7) for i in data["sdate"]]
        data["weekyear"] = week_order
    #seasons特征-表示季节
    def create_season(self, data):  # 将12个月分为供热，供冷，其他三类，输出两列
        """
        seasons 1 是夏季，2是冬季，3是其他
        :param data: df,load_data 的输出X
        """
        if data.empty:
            logger.error('传入数据为空')
        gp = load.Graph_load('mysql_conn.cnf')
        #对于每天分别获取图谱ID,根据图谱ID判断季节
        season_list = []
        for i in data["sdate"].values:
            item_str = i.strftime("%Y-%m-%d")
            graph_type = gp.load_graph_type(item_str)
            if graph_type==1:
                season_list.append(1)
            elif graph_type==3:
                season_list.append(2)
            else:
                season_list.append(3)
        data["season"] = season_list
    # create_m_average特征
    def create_m_average(self,data, column_name):  # 以月为条件计算温度均值
        """
        m_average列，float,表示温度max或温度min月截止日前的均值
        :param data: df,load_data 的输出X
        :param column_name:str max或min 表示day_t_max和day_t_min列
        """
        ld = load.Data_load()
        data["month"] = [int(i.strftime('%m')) for i in data["sdate"]]
        df_mean = ld.load_month_mean(column_name)
        data = pd.merge(data, df_mean, how='left', on='month')
        data["m_average_" + column_name] = data[column_name].copy(deep = False)
        data.drop("month", axis=1, inplace=True)
        data.drop(column_name[-3:], axis=1, inplace=True)
        return data
    #根据code计算8_18时均值特征
    def get_train_data(self, data, dev_code):
        """
        生成code 8_18时均值特征
        :return:data：df,数据集
                dev_code：要取的设备code
        """
        # 根据数据集开始结束日期获取真实数据
        db = mysql_conn.db_con()
        data['sdate'] = pd.to_datetime(data['sdate']).dt.date  # sdate变成日期
        sqlstr1 = r"select * from data_rt where sdate >= %s and sdate <= %s  and dev_code = %s"
        df = pd.read_sql(sqlstr1, db, params=[data["sdate"][0], data["sdate"][-1], dev_code])  # 获取训练数据标签及日期
        db.close()
        # 计算8——18时均值，并按日期和data表连接
        df["hour"] = [1 if i >= 8 and i < 18 else 0 for i in
                      pd.to_datetime(data['sdate']).dt.hour.values]  # 八点到十八点标识1,否则是0
        df['sdate'] = pd.to_datetime(df['sdate']).dt.date  # sdate变成日期
        df_group = df.groupby(['sdate', "hour"], as_index=False).mean()  # 按每日八点前和非八点前将数据求和
        df_group = df_group.pivot_table(index='sdate', columns='hour', values='data')  # 将八点前和非八点前的日数据变成列,sdate变成index
        df_group.reset_index(inplace=True)  # 重置index
        df_group["8_18%s" % (dev_code)] = df_group[1]  # data 等于八点前与非八点前的和
        data = pd.merge(data, df_group, how='left', on='sdate')
        return data
    #生成数据集
    def build_data(self,start_date, end_date,dev_code):
        """
        根据原始数据集构建特征-生成数据集
        :param start_date: 开始时间，str,形如 20200101
        :param end_date:   结束时间，str,形如 20200101
        :param dev_code:   str ,形如 YC1007
        :return:
            data：dataframe ,数据集
            data_label：dataframe ,数据集标签
        """
        #读取.ini文件
        ini_path = cur_path + '/model/model_%s/model_%s.ini' % (dev_code,dev_code)  # 定制model.ini文件地址
        if not os.path.exists(ini_path):
            ini_path = cur_path + '/common/model.ini'
        try:
            config.read(ini_path, "utf-8")
        except IOError as e:
            logging.info(" %s!", e)
        # 读取天级特征名列表
        feature = config['feature']['day_feature']
        fea_list = feature.split(",")
        fea_list=["sdate"]+fea_list
        #读取天气数据
        ld = load.Data_load()
        data_df = ld.load_day_weather(start_date, end_date)
        #匹配天级参数，并删除多余参数
        col = data_df.columns  #获取天气df列名
        drop_col = [ele for ele in col if ele not in fea_list] #获取天气df列名中不在fea_list中的列名
        data_df.drop(drop_col, axis=1, inplace=True) #删除不匹配列名
        #读取原始数据集及标签
        data = ld.load_data_day(start_date, end_date,dev_code)[0]  #获取数据集
        data_label = ld.load_data_day(start_date, end_date,dev_code)[1] #获取标签
        #根据fea_list特征列表-生成新特征
        if "day_t_mean" in fea_list: #生成day_t_mean列
            data["day_t_mean"] = (data["day_t_max"] + data["day_t_min"]) / 2
        if 'holiday' in fea_list:    #生成holiday列
            self.create_holidays( data)
        if 'season' in fea_list:    #生成seasons列
            self.create_season( data)
        if 'holiday_plus_season' in fea_list: #生成holiday_plus_seasons列
            data["holiday_plus_season"] = data["holiday"]+ data["season"]
        if 'weekdays' in fea_list:
            self.create_weekdays(data)
        if 'm_average_day_t_max' in fea_list:
            data = self.create_m_average(data, "day_t_max")
        if 'm_average_day_t_min' in fea_list:
            data = self.create_m_average(data, "day_t_min")
        if 'weekday' in fea_list:    #生成seasons列
            self.create_weekdays( data)
        # 对于列进行重排序
        data = data[fea_list]
        return data,data_label
    #拼接文件,目前没有使用
    def join_file(self, data, file_path, join_file):
        """
        将用户输入拼接入数据集
        :param data: 原数据集
        :param file_path: 能、电量路径
        :param join_file: 拼接文件名
        :return: 拼接后data
        """
        # 拼接文件非空
        if join_file is not None:
            # 检测文件存在
            join_file_path = cur_path + '/joinfile/join' + file_path + os.sep + join_file
            if os.path.exists(join_file_path):
                # 拼接文件入data
                df_join = pd.read_csv(join_file_path)
                if 'sdate' in df_join.columns:
                    # csv日期格式转换
                    df_join['sdate'] = pd.to_datetime(df_join['sdate'])
                    data = pd.merge(data, df_join, on='sdate')
                else:
                    logger.error('拼接文件无sdate列，拼接失败')
                    raise ValueError('拼接文件无sdate列，拼接失败')
            else:
                logger.error('未发现join_file path:%s，拼接失败' % join_file_path)
                raise ValueError('未发现join_file path:%s，拼接失败' % join_file_path)
        return data
class Device_util:
    """
    策略算法中的数据处理功能
    """
    def __init__(self,graph_id,date=None,stgy_type=None):
        lg = load.Graph_load()
        self.graph_id = graph_id #图谱id
        if stgy_type is None:
            self.stgy_type = lg.load_strategy_type(graph_id)
        else:
            self.stgy_type = stgy_type
        self.date = date #datetime
    #######################################################################
    #经济优先模式下的工况筛选
    def economic_first(self):
        relat_info_init = self.relation_info()  # 策略模型工况的属性
        return relat_info_init
    #能量优先模式下的工况筛选
    def energy_first(self):
        relat_info_init = self.relation_info()  # 策略模型工况的属性
        relat_info = relat_info_init[relat_info_init["type_id_to"] == 3][
            relat_info_init["type_id_from"] == 1].reset_index(drop=True)  # 筛选主机直供工况
        return relat_info
    #三种模式下的工况筛选函数合并
    def first_choice(self,type):
        types = {
            1001: self.economic_first,
            1002: self.energy_first,
            1003: self.economic_first,
        }
        method = types.get(type)
        result = method()
        return result
    ###############################
    # 经济优先模式下的工况筛选
    def economic_first_com(self,relat_info):
        ld = load.Device_load()
        relation_com_init = ld.load_relation_com(self.graph_id)  #工况并行表
        return relation_com_init
    #能量优先模式下的并行工况筛选
    def energy_first_com(self,relat_info):
        ld = load.Device_load()
        relation_com = ld.load_relation_com(self.graph_id)  # 工况并行表
        for item in set(list(relation_com["stgy_code"].values)):  # 根据筛选后的直供工况，筛选并行工况
            merge_stgy = pd.merge(relat_info, relation_com[relation_com["stgy_code"] == item], how='inner', on=["device_from", "device_to"])
            if merge_stgy.shape[0] <= 1:
                relation_com.drop(index=(relation_com[relation_com["stgy_code"] == item].index), inplace=True)
        return relation_com
    # 三种模式下的工况筛选函数合并
    def first_choice_com(self, type,relat_info):
        types = {
            1001: self.economic_first_com,
            1002: self.energy_first_com,
            1003: self.economic_first_com,
        }
        method = types.get(type,relat_info)
        result = method(relat_info)
        return result
    #舒适优先模型，对已预测工况的调整
    def economics_first(self,day_define, dict, rel_com):
        """
        实际并行放能时间段内，所有并行工况的power值替换为实际并行工况在该时段整体均值
        :param day_define:
        :param dict: 工况字典
        :param rel_com:并行工况df
        :return:
        """
        for item in set(list(rel_com["stgy_code"].values)):  # 对每一组并行工况
            device_tuple = [(rel_com[rel_com["stgy_code"] == item]["device_from"][i], rel_com[rel_com["stgy_code"] == item]["device_to"][i]) for i in
                            range(len(rel_com[rel_com["stgy_code"] == item]))]  # 获取当前并行工况的设备id 元组组成的列表
            for tuple_item in device_tuple:
                if tuple_item not in list(dict.keys()):
                    dict[tuple_item] =[0 for i in range(len(day_define))]

            work_sign = [int(i) for i in rel_com[rel_com["stgy_code"] == item]["work_time"][0].split("-")]  # 当前并行工况工作时间[23,11]
            work_index = [day_define.index(i) for i in work_sign]  # 工作的开始结束时间在day_define中的index
            work_hours = np.array([1 if l >= work_index[0] or l <= (work_index[1] - 1) else 0 for l in range(len(day_define))])  # 并行工况的工作时间标记为1
            value_index = np.sum([np.array(dict[i]) * work_hours for i in device_tuple], axis=0)
            value_index_1 = np.array([0 if i == 0 else 1 for i in value_index])  # 实际并行放能小时标志,实际并行的小时为1
            mean_power = sum(value_index) / (sum(value_index_1) * len(device_tuple))  # 求取实际并行时段power均值
            value_new = np.array([round(i, 1) for i in value_index_1 * mean_power])  # 实际并行时段内平均后的各小时power数组
            for i in device_tuple:  # 对于每个工况
                value_index_0 = np.array([0 if i == 1 else 1 for i in value_index_1])  # 非实际并行，值为1
                valre_other = value_index_0 * np.array(dict[i])  # 非实际并行原power数组为原值，实际并行power为0
                dict[i] = list(value_new + valre_other)  # 将实际并行时段内的power替换为均值，其他时间不变
        return dict
    #计算昨日室内温度舒适性类别
    def comfort_type(self,dev_code,date=None,temp_code=None):
        """
        如果昨日室内温度在舒适性范围内,类型为0,如果供能偏少类型为-1,如果供能偏多类型为1
        :param dev_code: 能量编码
        :param date:   预测日
        :param temp_code: 内温编码
        :return: 舒适性类型,int -1:供能偏少  0:供能合适  1:供能偏多
        """
        ld = load.Data_load()
        inner_real = ld.load_temp_indoor(date, temp_code)
        inner_stand ,graph_type= ld.load_temp_goal(dev_code, date)

        if inner_real<inner_stand[0]: #偏冷
            if graph_type == 1:#夏季
                comf_type = 1
            elif graph_type == 3:#冬季
                comf_type = -1
            else:
                comf_type = 0
        elif inner_real >inner_stand[1]: #偏热
            if graph_type == 1:  #夏季
                comf_type = -1
            elif graph_type == 3:  # 冬季
                comf_type = 1
            else:
                comf_type = 0
        else:
            comf_type = 0
        return comf_type
    #根据昨日室内温度的舒适性类型,更新今日预测能量值
    def update_energy(self,comf_type,energy_pre,update_rate=0.1):
        """
        根据前一日室内温度的舒适类型,更新今日预测的能量值
        :param comf_type: 舒适度类型,int -1:供能偏少  0:供能合适  1:供能偏多
        :param energy_pre: 预测的能量值,float/int
        :param update_rate: 更新的比率,float [0,1]
        :return: 更新后的预测能量值float/int
        """
        if comf_type == -1:
            new_pre = energy_pre*(1+update_rate)
        elif comf_type == 1:
            new_pre = energy_pre*(1-update_rate)
        else:
            new_pre = energy_pre
        return new_pre
    #######################################################################
    #预测日的小时定义列表
    def day_define(self):
        """
        根据图谱结束时间,生成长为24的list,预测日的小时定义,[17,18,19,20,21,22,23,0,1,......15,16]
        :return:list,[17,18,19,20,21,22,23,0,1,......15,16]
        """
        lg = load.Graph_load()
        work_end = lg.load_graph_worktime(int(self.graph_id))["end_work_time"]  # 图谱工作结束时间
        return [i + work_end for i in range(24 - work_end)] + [i for i in range( work_end)]  # 预测日的小时定义,[17,18,19,20,21,22,23,0,1,......15,16]
    #根据当前图谱设备的是否纳入决策属性，分别获取策略模型的工况和条件模型的工况
    def relation_split(self):
        """
        根据当前图谱设备的是否纳入决策属性,分别获取策略模型的关系对和条件模型的工况
        :return:
            relat_condit : dataframa columns = [device_from, device_to, loss_rate, rel_name]
                                     type = [int,int,float,str]
                                     example = [3 ,20, 0.0 ,水蓄能蓄能]
            relat_strate : dataframa 同上
        """
        ld = load.Device_load(self.date)
        dev = ld.load_device(int(self.graph_id))  #当前图谱下所有设备
        dev0 = dev[dev["start_button"] == 0]  # 策略模型设备及属性信息
        dev1 = dev[dev["start_button"] >= 1]  # 条件模型设备及属性信息
        relat = ld.load_relation(int(self.graph_id))  # 当前图谱下的所有关系对
        #取当前图谱下-条件模型关系对
        relat_condit = relat[(relat["device_from"].isin(dev1["device_id"].tolist())) | ( relat["device_to"].isin(dev1["device_id"].tolist()))]
        #取当前图谱下-策略模型关系对
        relat_strate = relat[(relat["device_from"].isin(dev0["device_id"].tolist())) & ( relat["device_to"].isin(dev0["device_id"].tolist()))]
        #空值检测
        if relat_strate.shape[0] == 0:
            logging.info("relation_strategy df is none！")
        if relat_condit.shape[0] == 0:
            logging.info("relation_strategy df is none！")
        return relat_strate, relat_condit
    #获取各工况在各时段是否工作的标记
    def work_state(self, worktime_from, worktime_to):
        """
        获取各工况在各时段是否工作的标记
        根据工况中from设备和to设备 work_time的交集,按不同时段,将当前工况,在各时段的工作状态进行标记,其中在当前时段工作标记为1，不工作标记为0
        #       时段1  时段2  时段3  时段4
        #工况1    1      0      1     0
        #工况2    0      0      0     0
        #工况3    1      0      1     1
        :param worktime_from: array,dataframe的一列, 值类型为str 如 '7_11' ,表示工况中from设备的工作时间
        :param worktime_to:   array,dataframe的一列, 值类型为str 如 '7_11' ,表示工况中to 设备的工作时间
        :return:
            work_state: array, shape(工况数,时段数) bool ,1表示当前工况在当前时段工作,0表示当前工况在当前时段不工作
            el_hours :  array, shape(1,时段数)  int ,表示每个时段包含小时数
            work_cross_hours: shape(1,工况数)   int ,表示每个工况的from to两设备工作时间交集的小时数
        """
        ld = load.Device_load()
        ##获得from 和to设备work_time开始时间的max值
        from_start = [int(i.split("-")[0]) if int(i.split("-")[0]) < 23 else -1 for i in  worktime_from]  # from设备开始时间
        to_start = [int(i.split("-")[0]) if int(i.split("-")[0]) < 23 else -1 for i in worktime_to]  # to设备开始时间
        start = list(map(lambda x, y: max(x, y), to_start, from_start))  # 取两开始时间中较晚的一个

        ##获得from 和to设备work_time结束时间的min值
        from_end = [int(i.split("-")[1]) for i in worktime_from]  # from设备的结束时间
        to_end = [int(i.split("-")[1]) for i in worktime_to]  # to设备的结束时间
        end = list(map(lambda x, y: min(x, y), from_end, to_end))  # 取两结束时间中较早的一个

        ##获得各电价时段的开始时间list和结束时间list,以及没时段含小时数
        elec = ld.load_electricity(int(self.graph_id))  # 各时段电价dataframe
        el_start = [int(i.split(":")[0]) if int(i.split(":")[0]) < 23 else -1 for i in elec["start_time"]]  # 电价时段的开始时间
        el_end = [int(i.split(":")[0]) for i in elec["end_time"]]  # 电价时段的结束时间
        el_hours = np.array(el_end) - np.array(el_start)  # 各电价时段包含的小时数

        ##计算各工况在各时段的工作状态
        work_state = np.array( [[1 if start[i] <= el_start[j] and end[i] >= el_end[j] else 0 for j in range(len(el_hours))] for i in  range(len(start))])  # 工作状态矩阵
        work_cross_hours = list(np.array(end) - np.array(start))  # 每个工况 from和to设备工作时间交集的小时数
        return work_state, el_hours, work_cross_hours
    #获取策略模型工况的各种属性信息dataframe
    def relation_info(self):
        """
        获取策略模型工况的各种属性信息dataframe
        :return:relat_info:dataframe 策略模型各工况的属性信息
        """
        ld = load.Device_load()
        relat_condit, _ = self.relation_split()  # 策略模型工况
        device = ld.load_device(int(self.graph_id))  # 获取设备属性df
        relat_info = pd.merge(relat_condit, device, how='left', left_on="device_from", right_on="device_id",  copy=True)  # 添加from设备的属性信息
        #如果有多主机且主机能效比一样，按功率从小到大的优先级，对主机能效比进行调整
        host_id = list(set(relat_info[relat_info["type_id"]==1]["device_id"].values)) #主机ID  【3,4】
        host_power = list(set(relat_info[relat_info["type_id"]==1]["power"].values)) #主机ID  【550,550】
        host_conver = list(set(relat_info[relat_info["type_id"]==1]["conversion_rate"].values)) #主机ID  【550,550】

        dict_host = {} #key 是 主机id，value 是[index]
        for item_d in range(len(host_conver)):
            if host_conver[item_d] in dict_host.keys():  # 将{"工况名：[power,......,power]"}写入字典
                dict_host[host_conver[item_d]] += [host_id[item_d]]
            else:
                dict_host[host_conver[item_d]] = [host_id[item_d]]
        for key in list(dict_host.keys()):
            if len(dict_host[key]) <2:
                del dict_host[key]

        if len(dict_host)>0:
            for key in dict_host.keys():
                id_list = dict_host[key] #key 是能效比值,id_list是能效比等于当前值的host id 【3,4，6】
                index_id = [host_id.index(i) for i in id_list]  #id_list 在host_id中的index [0,1]
                power_list = np.array(host_power)[index_id] #[550,700,550]
                power_sort = sorted(range(len(power_list)), key=lambda k: power_list[k],reverse=True) #[0,2,1] power_list倒序排序后的原index
                for item_sort in range(len(power_sort)):
                    id_item = id_list[power_sort[item_sort]] #
                    relat_info.loc[relat_info["device_id"] == id_item, ['conversion_rate']] = key+item_sort
        relat_info = pd.merge(relat_info, device, how='left', left_on="device_to", right_on="device_id",  suffixes=('_from', '_to'), copy=True)  # 再添加to设备的属性信息
        return relat_info
    #获取产能设备的各种属性
    def cap_info(self):
        """
        获取产能设备的各种属性
        :return:
           cap_dict：dict,
                     conversion_rate: int 产能设备能效比均值
                     loss_rate:  float 当工况由产能设备输出时的损失率均值
                     power: array  各产能设备的功率数组
                     hours: array  各产能设备的工作小时数数组
                     cap: int  产能设备的产能上限和
        """
        relat_info = self.relation_info()  # 策略模型工况的属性
        cap_convert = relat_info[(relat_info["type_id_from"] == 1) | (relat_info["type_id_to"] == 2)][
            "conversion_rate_from"].mean()  # 产能设备的能效比(为蓄能设备蓄过能的)
        cap_loss = relat_info[(relat_info["type_id_from"] == 1) | (relat_info["type_id_to"] == 2)][
            "loss_rate"].mean()  # 产能设备的loss_rate(为蓄能设备蓄过能的)
        cap_dict = {}

        id_cap = list(set(relat_info[relat_info["type_id_from"] == 1]["device_from"].tolist()))  # 产能设备的id集合
        dict_cap = {}  # 各产能设备的属性字典
        for i in range(relat_info.shape[0]):
            key_i = relat_info["device_from"].values[i]  # 第i个工况的from设备id
            if key_i not in dict_cap.keys() and key_i in id_cap:
                power_i = relat_info["power_from"].values[i]  # index 0
                work_time_i = relat_info["work_time_from"].values[i]  # index 1
                cap_i = relat_info["cap_from"].values[i]  # index 2
                dict_cap[key_i] = [power_i, work_time_i, cap_i]
        cap_power = np.array([dict_cap[i][0] for i in dict_cap.keys()])  # 产能设备的功率 array
        cap_work_time = [[int(j) for j in dict_cap[i][1].split("-")] for i in dict_cap.keys()]
        cap_work_time = [[-1 if j == 0 and i[j] == 23 else i[j] for j in range(2)] for i in cap_work_time]  # 获取产能设备的工作时间
        cap_hours = np.array([i[1] - i[0] for i in cap_work_time])  # 产能设备的工作小时数
        cap_cap = np.array([dict_cap[i][2] for i in dict_cap.keys()])  # 产能设备上限
        cap_dict["conversion_rate"] = cap_convert
        cap_dict["loss_rate"] = cap_loss
        cap_dict["power"] = cap_power
        cap_dict["hours"] = cap_hours
        cap_dict["cap"] = cap_cap
        return cap_dict
    #得到各工况的并行标记,如果无并行为0,并行为1
    def parallel_mark(self,relation_com,relat_info):
        """
        得到各工况的并行标记,如果无并行为0,并行为1
        :return:
           parallel_mark: list ,形如[0,1,1]
        """
        # 获取并行标记
        if len(relation_com)>=2:
            relation_com["key"] = [str(relation_com["device_from"].values[i]) + "_" + str(relation_com["device_to"].values[i]) for i in range(relation_com.shape[0])]  # 使用from设备id和to设备id组成表key
            relat_info["key"] = [str(relat_info["device_from"].values[i]) + "_" + str(relat_info["device_to"].values[i]) for i in range(relat_info.shape[0])]  # 使用from设备id和to设备id组成表key
            relation_merge = pd.merge(relat_info, relation_com, on=["key"], how="left")  # 连接并行表和属性表
            relation_merge.fillna(value=0, inplace=True)  # 将空值填充为0
            parallel_mark = [int(i) for i in relation_merge["stgy_code"].tolist()]  # 获取工况的并行标记，如果无并行为0，并行为1,2,......
            parallel_time = [0 if i == 0 else [int(j) for j in i.split("-")] for i in relation_merge["work_time"].tolist()]  # 获取工况的并行开始结束时间
            parallel_time = [[-1, i[1]] if i != 0 and i[0] == 23 else i for i in parallel_time]
        else:
            parallel_mark = [0 for i in range(len(relat_info))]
            parallel_time = [0 for i in range(len(relat_info))]
        return parallel_mark, parallel_time
    #将从数据库读取的策略df转换为dict
    def strategy_to_dict(self, strate_df):
        """
        将从数据库读取的策略df转换为dict
        :param strate_df:
        :return:
        """
        strate_dict = {}  # 策略升级字典,key是工况名称,info;值是1*24的np.array;表示从17时至转日16时各小时的输出功率
        for i in range(strate_df.shape[0]):  # 对于df_data中的每条数据
            power = strate_df["power"].values[i]  # 取当前工况的power,float
            start = strate_df["start_time"].values[i]  # 取当前工况的开始时间,int,标志小时
            end = strate_df["end_time"].values[i]  # 取当前工况的结束时间,int,标志小时
            index_start = self.day_define().index(start)  # 开始时间在day_define中的索引
            index_end = self.day_define().index(end)  # 结束时间在day_define中的索引
            info = (strate_df["device_from"].values[i], strate_df["device_to"].values[i])
            if index_end >= index_start:
                item = np.array( [power if j >= index_start and j < index_end else 0 for j in range(len(self.day_define()))]).astype( 'float64')  # 对应day_define列表的,1*24的np.array;表示从17时至转日16时各小时的输出功率
            else:
                item = np.array( [power if j >= index_start or j < index_end else 0 for j in range(len(self.day_define()))]).astype( 'float64')  # 对应day_define列表的,1*24的np.array;表示从17时至转日16时各小时的输出功率
            if info in strate_dict.keys():  # 将{"工况名：[power,......,power]"}写入字典
                strate_dict[info] += item
            else:
                strate_dict[info] = item
        return strate_dict
    #根据设备id获取设备名称
    def get_device_name(self,device_id):
        """
        根据设备id获取设备名称
        :param device_id: int,设备id
        :return: device_name,str,设备名称
        """
        ld = load.Device_load()
        device_name = ld.load_device(int(self.graph_id))[ld.load_device(int(self.graph_id))['device_id'] == device_id]["device_name"].values[0]
        if len(device_name)==0:
            logging.error("get device_name error !")
        return device_name
    #合并升级策略字典与新预测策略（或真实执行策略）合并
    def merge_upgrade(self,dict_upgrade, dict_strat):
        """
        工况升级策略与预测策略(或真实的执行策略)合并时,取工况升级的时段，替换预测策略(或真实的执行策略)的整个对应时段
        :param dict_upgrade: 升级工况字典 字典,{"工况名：[power,......,power]"},升级工况字典,key是工况的名称,values是1*24 array,每个值代表[17,18,......,15,16]时的power
        :param dict_strat:  预测工况字典 字典,{"工况名：[power,......,power]"},新预测工况字典,key是工况的名称,values是1*24 array,每个值代表[17,18,......,15,16]时的power
        :return:
            dict_com：合并工况字典 字典,{"工况名：[power,......,power]"},合并工况字典,key是工况的名称,values是1*24 array,每个值代表[17,18,......,15,16]时的power
        """
        index_upgrade = []  # 获取升级工况有值处在列表中的索引
        for i in dict_upgrade.keys():  # 对于升级工况字典的所有value,将所有有值的索引存在列表index_upgrade中
            index_upgrade += [j for j in range(len(dict_upgrade[i])) if dict_upgrade[i][j] > 0]
        index_upgrade = list(set(index_upgrade))
        index_upgrade.sort()
        dict_com = {}  # 合并字典
        key_set = list(set(list(dict_upgrade.keys()) + list(dict_strat.keys())))  # 获取两个字典的键集合列表
        for i in key_set:  # 对于键集合中的每个值
            if i in dict_upgrade.keys() and i in dict_strat.keys():  # 如果升级字典和预测字典都含有该工况
                dict_com[i] = [dict_upgrade[i][j] if any([j == k for k in index_upgrade]) else dict_strat[i][j] for j in range(len(dict_strat[i]))]
            elif i in dict_upgrade.keys():  # 如果升级字典有该工况,预测字典没有,则直接保留升级工况
                dict_com[i] = dict_upgrade[i]
            elif i in dict_strat.keys():  # 如果预测字典有该工况,升级字典没有,则将升级工况有值的对应部分修改为0
                dict_com[i] = [0 if any([j == k for k in index_upgrade]) else dict_strat[i][j] for j in range(len(dict_strat[i]))]
        return dict_com
    #真实策略与新策略(或升级策略更新后的新策略)合并
    def merge_actual(self, dict_actual, dict_strat,save = True):
        """
        真实策略与新预测策略合并,需要将执行策略已执行时间段与预测策略的未执行时间段拼接
        :param dict_actual: 执行策略字典,字典,{"工况名：[power,......,power]"},执行工况字典,key是工况的名称,values是1*24 array,每个值代表[17,18,......,15,16]时的power
        :param dict_strat:  预测（或预测与升级工况的合并）字典,{"工况名：[power,......,power]"},新预测或合并的工况字典,key是工况的名称,values是1*24 array,每个值代表[17,18,......,15,16]时的power
        :return:
            dict_cat_new：执行策略与预测（或预测与升级工况合并）的合并策略字典，{"工况名：[power,......,power]"},新预测或合并的工况字典,key是工况的名称,values是1*24 array,每个值代表[17,18,......,15,16]时的power
        """
        next_hour = (datetime.datetime.now() + datetime.timedelta(hours=1)).hour  # 下一小时的值,int
        key_set = list(set(list(dict_actual.keys()) + list(dict_strat.keys())))  # 获取两个字典的键集合列表
        dict_cat_new = {}  # 定义合并字典
        pass_index = [i for i in range(len(self.day_define())) if i < self.day_define().index(next_hour)]  # 已经执行的时间列表
        future_index = [i for i in range(len(self.day_define() )) if i >= self.day_define().index(next_hour)]  # 未执行的时间列表
        for i in key_set:  # 工况名(字典的键)
            if i in dict_actual.keys() and i in dict_strat.keys():  # 如果该key在两个字典中都存在
                item_actual = np.array([dict_actual[i][j] if j in pass_index else 0 for j in range(len(dict_actual[i]))])  # 真实策略取过去时间段
                item_strat = np.array([dict_strat[i][j] if j in future_index else 0 for j in range(len(dict_strat[i]))])  # 新策略取未来时间段
                dict_cat_new[i] = item_actual + item_strat
            elif i in dict_actual.keys():  #如果键只在真实策略中,不在新策略中
                if save == True:
                    item_actual = dict_actual[i] #
                    dict_cat_new[i] = item_actual  #未来不变
                else:
                    item_actual = np.array([dict_actual[i][j] if j in pass_index else 0 for j in range(len(dict_actual[i]))])  # 真实策略取过去时间段
                    dict_cat_new[i] = item_actual  #未来改为0
            elif i in dict_strat.keys():  # 如果键只在新策略中,不在实际策略中
                item_strat = np.array([dict_strat[i][j] if j in future_index else 0 for j in range(len(dict_strat[i]))])
                dict_cat_new[i] = item_strat  # 新策略过去部分改为0
        return dict_cat_new
        # 将字典数据转换为数据库输出格式list
    #将列表按{means:[index]},以0切分
    def split_and_average(self,list_1):
        """
        将列表按{means:[index]},以0切分
        :param list_1:
        :return:
        """
        import random
        dict_mean = {}
        count = 0
        index_list = []
        for i in range(len(list_1)):
            if list_1[i] > 0 and i < (len(list_1) - 1):  # 如果当前值大于0,且不是最后一个值
                count += list_1[i]
                index_list.append(i)
            elif list_1[i] > 0 and i == (len(list_1) - 1):  # 如果当前值大于0,且是最后一个值
                count += list_1[i]
                index_list.append(i)
                mean_d = count / len(index_list)
                mean_d = mean_d + (0.5 - random.random())
                dict_mean[mean_d] = index_list
                count = 0
                index_list = []

            elif list_1[i] == 0:
                if i > 0 and list_1[i - 1] > 0:  # 如果当前为0 ，前次不为0输出
                    mean_d = count / len(index_list)
                    mean_d = mean_d +(0.5 - random.random())
                    dict_mean[mean_d] = index_list
                    count = 0
                    index_list = []
        return dict_mean
    #将列表按{power:[index]},以0切分
    def split_and_power(self, list1):
        """
        按功率切割工况
        :param list1: 工况24小时powerlist
        :return: dict_power:dict,(from_device_id,to_device_id):[...]
        """
        import random
        dict_power = {}
        index_list = []
        for i in range(len(list1)):
            if i == 0:  # 当前是第一个值
                if list1[i] > 0:
                    index_list.append(i)
                else:
                    pass
            else:
                if list1[i] > 0 and list1[i - 1] > 0 and list1[i] != list1[i - 1]:  # 如果当前值和前一个值都大于0 且不相等
                    power = list1[i - 1]
                    power = power + (0.5 - random.random())
                    dict_power[power] = index_list
                    index_list = []
                    index_list.append(i)
                    if i == (len(list1) - 1):
                        power = list1[i]
                        power = power + (0.5 - random.random())
                        dict_power[power] = index_list
                        index_list = []
                elif list1[i] > 0 and list1[i - 1] == 0:  # 如果当前值大于0，前一个值等于0
                    index_list = []
                    index_list.append(i)
                    if i == (len(list1) - 1):
                        power = list1[i]
                        power = power + (0.5 - random.random())
                        dict_power[power] = index_list
                        index_list = []
                elif list1[i] > 0 and list1[i] == list1[i - 1]:  # 如果当前值大于0 且等于前一个
                    index_list.append(i)
                    if i == (len(list1) - 1):
                        power = list1[i]
                        power = power + (0.5 - random.random())
                        dict_power[power] = index_list
                        index_list = []
                elif list1[i] == 0 and list1[i - 1] > 0:  # 如果当前为0 但前一个值非0
                    power = list1[i - 1]
                    power = power + (0.5 - random.random())
                    dict_power[power] = index_list
                    index_list = []
        return dict_power
    def strategy_to_list_power(self, dict, model_version,date):
        """
        将字典形式的工况转换为合适输出到数据库的二维列表形式
        :param dict: 待转换的字典{工况名：[power,......,power]}
        :param model_version: 模型版本,int
        :param graph_id: 图谱id,int
        :return:
            temp_list：工况输出信息二维列表,其中子列表形如[开始时间, 结束时间, power, info, 图谱id, 策略类型, 模型版本,创建时间]
        """
        ld = load.Device_load()
        device = ld.load_device(int(self.graph_id))  # from设备的启动间隔时间
        temp_list = []  # 工况输出信息保存列表定义
        for i in dict.keys():  # 对于字典中的每个key(info工况)
            inter_from = device[device["device_id"] == i[0]]["interval_time"].values
            interval_i = int(inter_from[0]) if len(inter_from) >0 else 0
            minut_int = 60 - interval_i if (60 - interval_i)<60 else 59
            d = self.split_and_power(dict[i]) #未来均值合并
            for key in d.keys():  #对于每个实际power
                ld = load.Device_load()
                date_day = ld.load_pre_date(date)
                start_date, end_date = ld.load_pre_time(date_day, self.day_define()[d[key][0]],self.day_define()[d[key][-1]])
                start_time = start_date.strftime("%Y%m%d%H%M%S")
                end_time = (end_date+datetime.timedelta(minutes=minut_int)).strftime( "%Y%m%d%H%M%S")
                info_start = start_date.strftime("%H:%M")
                info_end = (end_date+datetime.timedelta(minutes=minut_int)).strftime( "%H:%M")
                if i[0] == 0:
                    info = "无策略建议"
                else:
                    device_from = self.get_device_name(i[0])
                    device_to = self.get_device_name(i[1])
                    info = info_start + "_" + info_end + device_from + "_" + device_to
                create_time = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")  # 写入temp表的数据创建时间
                temp_item = [start_time, end_time, int(round(key)), int(i[0]), int(i[1]), info, int(self.graph_id), int(self.stgy_type), int(model_version), create_time]  # 每个工况写入数据库的信息列表
                temp_list.append(temp_item)
        return temp_list
    # #将字典转换为输出到数据库格式的列表
    # def strategy_to_list_mean(self, dict, model_version, date):
    #     """
    #     将字典形式的工况转换为合适输出到数据库的二维列表形式
    #     :param dict: 待转换的字典{工况名：[power,......,power]}
    #     :param model_version: 模型版本,int
    #     :param graph_id: 图谱id,int
    #     :return:
    #         temp_list：工况输出信息二维列表,其中子列表形如[开始时间, 结束时间, power, info, 图谱id, 策略类型, 模型版本,创建时间]
    #     """
    #     ld = load.Device_load()
    #     device = ld.load_device(int(self.graph_id))  # from设备的启动间隔时间
    #     temp_list = []  # 工况输出信息保存列表定义
    #     for i in dict.keys():  # 对于字典中的每个key(info工况)
    #         inter_from = device[device["device_id"] == i[0]]["interval_time"].values
    #         interval_i = int(inter_from[0]) if len(inter_from) > 0 else 0
    #         minut_int = 60 - interval_i if (60 - interval_i) < 60 else 59
    #         d = self.split_and_average(dict[i])  # 均值合并
    #         for key in d.keys():  # 对于每个实际power
    #             ld = load.Device_load()
    #             date_day = ld.load_pre_date(date)
    #             start_date, end_date = ld.load_pre_time(date_day, self.day_define()[d[key][0]], self.day_define()[d[key][-1]])
    #             start_time = start_date.strftime("%Y%m%d%H%M%S")
    #             end_time = (end_date + datetime.timedelta(minutes=minut_int)).strftime("%Y%m%d%H%M%S")
    #             info_start = start_date.strftime("%H:%M")
    #             info_end = (end_date + datetime.timedelta(minutes=minut_int)).strftime("%H:%M")
    #
    #             if i[0] == 0:
    #                 info = "无策略建议"
    #             else:
    #                 device_from = self.get_device_name(i[0])
    #                 device_to = self.get_device_name(i[1])
    #                 info = info_start + "_" + info_end + device_from + "_" + device_to
    #             create_time = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")  # 写入temp表的数据创建时间
    #             temp_item = [start_time, end_time, int(round(key)), int(i[0]), int(i[1]), info, int(self.graph_id),
    #                          int(self.stgy_type), int(model_version), create_time]  # 每个工况写入数据库的信息列表
    #             temp_list.append(temp_item)
    #     return temp_list
    # 将字典转换为输出到数据库格式的列表
    def strategy_to_list_mean(self, dict, model_version, date):
        """
        将字典形式的工况转换为合适输出到数据库的二维列表形式
        :param dict: 待转换的字典{工况名：[power,......,power]}
        :param model_version: 模型版本,int
        :param graph_id: 图谱id,int
        :return:
            temp_list：工况输出信息二维列表,其中子列表形如[开始时间, 结束时间, power, info, 图谱id, 策略类型, 模型版本,创建时间]
        """
        ld = load.Device_load()
        device = ld.load_device(int(self.graph_id))  # from设备的启动间隔时间
        temp_list = []  # 工况输出信息保存列表定义
        dict_mean = {}
        for i in dict.keys():  # 对于字典中的每个key(info工况)
            inter_from = device[device["device_id"] == i[0]]["interval_time"].values
            interval_i = int(inter_from[0]) if len(inter_from) > 0 else 0
            minut_int = 60 - interval_i if (60 - interval_i) < 60 else 59
            d = self.split_and_average(dict[i])  # 均值合并
            for key in d.keys():  # 对于每个实际power
                ld = load.Device_load()
                date_day = ld.load_pre_date(date)
                start_date, end_date = ld.load_pre_time(date_day, self.day_define()[d[key][0]],
                                                        self.day_define()[d[key][-1]])
                start_time = start_date.strftime("%Y%m%d%H%M%S")
                end_time = (end_date + datetime.timedelta(minutes=minut_int)).strftime("%Y%m%d%H%M%S")
                info_start = start_date.strftime("%H:%M")
                info_end = (end_date + datetime.timedelta(minutes=minut_int)).strftime("%H:%M")

                if i[0] == 0:
                    info = "无策略建议"
                else:
                    device_from = self.get_device_name(i[0])
                    device_to = self.get_device_name(i[1])
                    info = info_start + "_" + info_end + device_from + "_" + device_to
                create_time = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")  # 写入temp表的数据创建时间
                temp_item = [start_time, end_time, int(round(key)), int(i[0]), int(i[1]), info, int(self.graph_id),
                             int(self.stgy_type), int(model_version), create_time]  # 每个工况写入数据库的信息列表
                temp_list.append(temp_item)
                #############################################
                if i in dict_mean.keys():
                    dict_mean[i] += np.array([key if i in d[key] else 0 for i in range(24)])
                else:
                    dict_mean[i] = np.array([key if i in d[key] else 0 for i in range(24)])
        return temp_list,dict_mean


class To_database:
    """
    将数据写入数据库
    """
    #输出到strategy_output表
    def to_output(self, output_data,date,stgy_type):
        """
        将策略合并结果写入output数据库
        :param output_data:二维list,形如[[start_time,end_time,power,device_from,device_to,info,graph_id,strategy_type],......,[]]
        """
        ##对输出策略列表按结束时间小时进行正序排序
        lg = load.Graph_load()
        item_str = date.strftime("%Y-%m-%d")
        gp = load.Graph_load('mysql_conn.cnf')
        graph_id = gp.put_databydate(item_str)
        work_end = lg.load_graph_worktime(int(graph_id))["end_work_time"]  # 图谱工作结束时
        output_data = sorted(output_data, key=(lambda x: [int(x[0][8:10]) if int(x[0][8:10])<work_end else int(x[0][8:10])-24 ,int(x[1][8:10]) if int(x[1][8:10])<work_end else int(x[1][8:10])-24]))

        ld = load.Device_load()
        date_day = ld.load_pre_date(date)
        start_time, end_time = ld.load_pre_time(date_day, work_end, work_end)
        with Session() as session:
            delete = 'DELETE FROM strategy_output where start_time>=%s and end_time <=%s and strategy_type =%s'
            session.change(delete, [start_time,end_time,stgy_type])
            insert1 = "INSERT INTO strategy_output(start_time,end_time,power,device_from,device_to,info,graph_id,strategy_type) VALUES(%s,%s,%s,%s,%s,%s,%s,%s)"
            try:
                if len(output_data) == 1:
                    session.change(insert1, output_data[0])
                else:
                    session.change_many(insert1, output_data)
            except Exception as e:
                logging.exception("Exception occurred")
                logging.error("to_output error!")
    #输出到strategy_temp表
    def to_temp(self, list_stra):
        """
        将策略合并结果写入strategy_temp数据库
        :param temp_data:二维list,形如[[start_time,end_time,power,info,graph_id,strategy_type,model_version,create_time],......,[]]
        """
        with Session() as session:
            insert2 = "INSERT INTO strategy_temp(start_time,end_time,power,device_from,device_to,info,graph_id,stgy_type,model_version,create_time) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
            try:
                if len(list_stra) == 1:
                    session.change(insert2, list_stra[0])
                else:
                    session.change_many(insert2, list_stra)
            except Exception as e:
                logging.exception("Exception occurred")
                logging.error("to_temp error!")
    #输出到history_data表
    def to_history(self, stay_type, dict_strate, graph_id,sdate):
        """
        将策略工况的设备信息写入history_data表
        :param
            stay_type:策略类型,str
            graph_id：图谱ID,str
        """
        ld = load.Device_load()
        date = ld.load_pre_date(sdate)
        info_path = ""
        node_list = []
        for key in dict_strate.keys():
            info_path = info_path + "#".join([str(key[0]), str(key[1])]) + ","
            node_list.append(str(key[0]))
            node_list.append(str(key[1]))
        info_node = ",".join(list(set(node_list)))
        alias_path = ["path", info_path, date, stay_type, int(graph_id)]
        alias_node = ["node", info_node, date, stay_type, int(graph_id)]
        history_data = [alias_path, alias_node]
        with Session() as session:
            delete = 'DELETE FROM history_data where sdate=%s and strategy_type=%s'
            session.change(delete, [date.strftime("%Y%m%d"),stay_type])
            insert3 = "INSERT INTO history_data(alias,data,sdate,strategy_type,graph_id) VALUES(%s,%s,%s,%s,%s)"
            try:
                session.change_many(insert3, history_data)
            except Exception as e:
                logging.exception("Exception occurred")
                logging.error("to_history error!")
    #输出到pre_data_hour表
    def to_hour(self, hour_data,date):
        """
        将小时趋势预测数据写入pre_data_hour表
        :param hour_data:  list:[[data,sdate,device_code,graph_id,stgy_type],......,[]]
        """
        start_time = date
        end_time = date+datetime.timedelta(days = 1)
        print("===============》start_time,end_time:", start_time,end_time)
        for k in hour_data:
            print(k)
        with Session() as session:
            delete = 'DELETE FROM pre_data_hour where sdate>=%s and sdate<%s'
            session.change(delete, [start_time.strftime("%Y%m%d"),end_time.strftime("%Y%m%d")])
            insert = "INSERT INTO pre_data_hour(data,sdate,device_code,graph_id,stgy_type) VALUES(%s,%s,%s,%s,%s)"
            try:
                session.change_many(insert, hour_data)
            except Exception as e:
                logging.exception("Exception occurred")
                logging.error("to_history error!")
    # 将预测结果写入数据库
    def to_predict_dl(self, hour_data,stgy_type):# TODO 写入dl
        """
        将小时趋势预测电量数据写入predict_dl表
        :param hour_data:  list:[[data,sdate,device_code,graph_id,stgy_type],......,[]]
        """
        with Session() as session:
            delete = 'DELETE FROM predict_dl where sdate=%s and model_version =%s and stgy_type =%s'
            session.change(delete, [hour_data[1].strftime("%Y%m%d"),hour_data[-1],stgy_type])
            insert = "INSERT INTO `predict_dl` (`data`, `sdate`, `graph_id`, `stgy_type`, `model_version`) VALUES (%s,%s,%s,%s,%s);"
            try:
                session.change(insert, hour_data)
            except Exception as e:
                logging.exception("Exception occurred")
                logging.error("to_predict_dl error!")
    def to_predict_data(self, hour_data,stgy_type):
        """
        将小时趋势预测能量数据写入predict_dl表
        :param hour_data:  list:[[data,sdate,device_code,graph_id,stgy_type],......,[]]
        """
        with Session() as session:
            delete = 'DELETE FROM predict_data where sdate=%s and model_version =%s and stgy_type =%s'
            session.change(delete, [hour_data[1].strftime("%Y%m%d"),hour_data[-1],stgy_type])
            insert = "INSERT INTO `predict_data` (`data`, `sdate`, `graph_id`, `stgy_type`, `model_version`) VALUES (%s,%s,%s,%s,%s)"
            try:
                session.change(insert, hour_data)
            except Exception as e:
                logging.exception("Exception occurred")
                logging.error("to_predict_dl error!")
    #输出到pre_data_day
    def to_pre_data_day(self,dev_code, pre_data,model_id):
        """
        将能量或电量预测结果写入数据库
        """
        gd = load.Graph_load()
        to_data = pre_data[["sdate", "data"]]
        to_data["device_code"] = dev_code
        to_data["graph_id"] = [gd.put_databydate(i) for i in pre_data["sdate"].values]
        to_data["model_version"] = model_id
        start_time = pre_data["sdate"].values[0]
        end_time = pre_data["sdate"].values[-1]
        with Session() as session:
            delete = 'DELETE FROM pre_data_day where sdate>=%s and sdate <= %s and device_code=%s and model_version=%s'
            session.change(delete, [start_time,end_time, dev_code,model_id])
            val = []
            for _, row in to_data.iterrows():
                val.append([row[k] for k in to_data.columns.tolist()])
            insert = "INSERT INTO pre_data_day(sdate,data,device_code,graph_id,model_version) VALUES(%s,%s,%s,%s,%s)"
            try:
                session.change_many(insert, val)
            except Exception as e:
                logging.exception("Exception occurred")
                logging.error("to_pre_data_day error!")
    def to_pre_strategy(self,list_stra,date,model_version,stgy_type):
        """
           将策略写入pre_strategy数据库
           :param temp_data:二维list,形如[[start_time,end_time,power,info,graph_id,strategy_type,model_version,create_time],......,[]]
           """
        ##对输出策略列表按结束时间小时进行正序排序
        lg = load.Graph_load()
        item_str = date.strftime("%Y-%m-%d")
        gp = load.Graph_load('mysql_conn.cnf')
        graph_id = gp.put_databydate(item_str)
        work_end = lg.load_graph_worktime(int(graph_id))["end_work_time"]  # 图谱工作结束时
        list_stra = sorted(list_stra, key=(lambda x: [int(x[0][8:10]) if int(x[0][8:10])<work_end else int(x[0][8:10])-24 ,int(x[1][8:10]) if int(x[1][8:10])<work_end else int(x[1][8:10])-24]))
        print("model_version:",model_version)
        ld = load.Device_load()
        start_time, end_time = ld.load_pre_time(date, work_end, work_end)
        print("start_time-:",start_time,end_time)
        with Session() as session:
            delete = 'DELETE FROM pre_strategy where start_time >= %s and end_time<=%s and model_version =%s and strategy_type =%s'
            session.change(delete, [start_time,end_time,model_version,stgy_type])
            insert2 = "INSERT INTO pre_strategy(start_time,end_time,power,device_from,device_to,info,graph_id,strategy_type,model_version) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s)"
            try:
                if len(list_stra) == 1:
                    session.change(insert2, list_stra[0])
                else:
                    session.change_many(insert2, list_stra)

            except Exception as e:
                logging.exception("Exception occurred")
                logging.error("to_pre_strategy error!")
if __name__ == '__main__':
    ###调参+脚本输入参数优化 的最终模型.py文件2.0
    # gp = Graph(conn_file)
    # gp.put_databydate('2021-09-22')
    #        graph_id = gp.put_databydate(self.start_time[:4] + "-" + self.start_time[4:6] + "-" + self.start_time[6:])
    #        gp.put_databyid(graph_id)
    # df = pd.read_csv('train/train_energy/测试_1.csv')
    # print(Data_util.join_file(1, df, join_file='1.csv')) # test joinfile

    #########
    dev_code = "ajdlnt.point56"
    date = datetime.datetime(2021,1,1,0,0,0)
    comf_type = Device_util(13).comfort_type(dev_code, date)
    new_pre = Device_util(13).update_energy(comf_type, 4000, update_rate=0.1)
    print("new_pre:",new_pre)